package cn.com.pansky.service;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.vsame.url2sql.action.CommonAction;
import net.vsame.url2sql.helper.WebHelper;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.HttpException;
import org.apache.commons.httpclient.NameValuePair;
import org.apache.commons.httpclient.methods.PostMethod;

import cn.com.pansky.helper.ZgssHelper;
import cn.com.pansky.payment.PayConf;
import cn.com.pansky.sdk.API;
import cn.com.pansky.util.AppUtils;
import cn.com.pansky.util.ByteToImageUtil;
import cn.com.pansky.util.DateUtil;
import cn.com.pansky.util.HttpClientUtil;
import cn.com.pansky.util.PropertiesUtil;

import com.abc.pay.client.JSON;
import com.abc.pay.client.ebus.SettleRequest;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.psbc.payment.client.SignatureService;
/**
 * 银行对账service
 * @author Administrator
 *
 */
public class BankStatementService extends CommonAction{
	
	/**
	 * 银行对账接口
	 */
	public void bankStatement() {
		String AAE140 = getParam("AAE140");//险种类型
		String AAB099 = getParam("AAB099");//对账方式 0自动；1手工
		String ITEAM = context.getParam("ITEAM");//商户号ITEAM
		String AAD017 = getParam("AAD017");//对账日期
		
		//邮储对前天帐，时间单独处理
		String AAD017_psbc =  ByteToImageUtil.getSpecifiedDayBefore(AAD017);
		
		JSONArray array = JSONArray.parseArray(ITEAM);
		
		List<Map<String, String>> YClist = new ArrayList<Map<String,String>>();
		List<Map<String, String>> NHlist = new ArrayList<Map<String,String>>();
		List<Map<String, String>> JHlist = new ArrayList<Map<String,String>>();
		for(int i = 0; i < array.size(); i++) {
			JSONObject obj = JSONObject.parseObject(array.get(i).toString());
			String AAE051 = obj.getString("AAE051"); // 商户号
			String BAB502 = obj.getString("BAB502"); // 银行类别
			String AAB301 = obj.getString("AAB301"); // 区划编码
			String BAE300 = obj.getString("BAE300"); // 银行秘钥
			String BAA400 = obj.getString("BAA400"); // 1银行，2微信，3支付宝
			String BAE400 = obj.getString("BAE400"); // appid
			Map<String, String> map = new HashMap<String, String>();
			if ("01".equals(BAA400)) { //银行对账
				if("YC".equals(BAB502) ||  "315".equals(BAB502) && null != AAE051 && !"".equals(AAE051)) {
					//邮储商户
					map.put("AAE051", AAE051);
					//区划编码
					map.put("AAB301", AAB301);
					//银行类别
					map.put("BAB502", BAB502);
					YClist.add(map);
				} else if("NH".equals(BAB502) ||  "103".equals(BAB502) && null != AAE051 && !"".equals(AAE051)) {
					//农行商户
					map.put("AAE051", AAE051);
					//区划编码
					map.put("AAB301", AAB301);
					//银行类别
					map.put("BAB502", BAB502);
					NHlist.add(map);
				}  else if("JH".equals(BAB502) ||  "105".equals(BAB502) && null != AAE051 && !"".equals(AAE051)) {
					//建行商户
					map.put("AAE051", AAE051);
					//商户秘钥
					map.put("BAE300", PayConf.SIGNKEY);
					//区划编码
					map.put("AAB301", AAB301);
					JHlist.add(map);
				}
			} else if ("02".equals(BAA400)) { //微信对账
				
			} else if ("03".equals(BAA400)) { //支付宝对账
				
			}
		}
		// 银行对账接口service
		BankStatementService bank = new BankStatementService();
		if (YClist.size()>0) {
			bank.psbcStatement(YClist, AAD017_psbc, AAE140, AAB099); // 邮储对账
		}
		if (NHlist.size()>0) {
			bank.abcStatement(NHlist, AAD017, AAE140, AAB099); // 农行对账
		}
		if (JHlist.size()>0) {
			bank.jhStatement(JHlist, AAD017, AAE140, AAB099); // 建行对账
		}
	}
	
	/**
	 * 邮储对账
	 */
	public void psbcStatement(List<Map<String, String>> YClist, String date, String AAE140, String AAB099) {
		if(YClist != null && YClist.size() > 0) {
			JSONArray resultArray_ = new JSONArray();
			for(Map<String, String> map : YClist) {
				String AAE051 = map.get("AAE051"); // 商户号
				String AAB301 = map.get("AAB301"); // 行政区划
				String BAB502 = map.get("BAB502"); //银行类别
				JSONArray resultArray = psbcExecute(AAE051, date, AAB301, BAB502); // 执行该商户的对账
				
				if (resultArray.size()>0) {
					for (int i = 0; i < resultArray.size(); i++) {
						resultArray_.add(resultArray.get(i));
					}
				}
			}
			if ("150".equals(AAE140)) {//居民养老
				
				if ("0".equals(AAB099)) {
					API.insertSysRA22(resultArray_);
				} else if ("1".equals(AAB099)) {
					context.getDatas().put("MSG", resultArray_);
					context.getDatas().put("FLAG", "SUCCESS");
				}
			} else if ("110".equals(AAE140)) {//职工养老
				String businessID = "008";
				try {
					ZgssHelper.zgssHttp(businessID, resultArray_.toString());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 邮储执行商户下的对账
	 * @param AAE051
	 */
	public JSONArray psbcExecute(String AAE051, String date, String AAB301, String BAB502) {
		String transName = "IDFR"; // 交易名称
		// 交易数据明文数据封装开始
		String MercCode = AAE051; // 商户号 必填  测试数据：1100529310001000003
		String OSttDate = date; // 当前日期的前一天 必填20140822
		String SetFType = "1"; // 对账文件类型0：全部，1：支付，3：退货
		// 交易数据明文数据封装结束
		// 交易数据明文
		String Plain = "MercCode="+MercCode+"|OSttDate="+OSttDate+"|SetFType="+SetFType;
		
		// 交易数据明文签名
		String Signature = SignatureService.sign(Plain);
		String resultStr= "";//要返回的response信息
		HttpClient httpClient = new HttpClient();
		PostMethod postMethod = new PostMethod(PayConf.PayUrl);
		//填入各个表单域的值
		NameValuePair[] data = {
			new NameValuePair("transName", transName),
			new NameValuePair("Plain", Plain),
			new NameValuePair("Signature", Signature),
		};
		// 将表单的值放入postMethod中
		postMethod.setRequestBody(data);
		try {
			// 执行postMethod
			System.out.println("邮储对账开始============================");
			httpClient.executeMethod(postMethod);
			resultStr= postMethod.getResponseBodyAsString();
			postMethod.releaseConnection();
		} catch (HttpException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		System.out.println(resultStr);
		System.out.println("邮储账单获取成功=============================");
		JSONArray resultArray = new JSONArray();
		if(resultStr.indexOf("<ErrorMsg>") != -1) {
			System.out.println(resultStr.substring(resultStr.indexOf("<ErrorMsg>")+10, resultStr.indexOf("</ErrorMsg>")));
		} else {
			// 返回商户对账文件
			String[] array = resultStr.split("\r\n");
			for(int i = 1; i < array.length; i++) {
				JSONObject obj = new JSONObject();
				String items = array[i].replace("|", ",");
				String[] itemsArray = items.split(",");
				obj.put("AAD017", itemsArray[2].toString().substring(0, 8)); // 交易日期
				String aaz288 = itemsArray[3].trim().substring(0, itemsArray[3].trim().length()-7);
				obj.put("AAZ288", aaz288); // 订单号
				obj.put("AAZ2881", itemsArray[3].trim()); // 订单号
				obj.put("AAD008", itemsArray[4].trim()); // 平台流水号
				obj.put("AAE051", itemsArray[5].trim()); // 商户号
				obj.put("AAE019", itemsArray[7].trim()); // 交易金额
				obj.put("AAE354", itemsArray[10].equals("00000000")?"1":"0"); // 回应码
				obj.put("AAE355", ""); // 错误信息
				obj.put("AAC002", ""); // 身份证号
				obj.put("AAZ295", BAB502); // 银行支付类别
				obj.put("AAE356", "0"); // 退款标识
				obj.put("AAC003", "");
				obj.put("AAB301", AAB301); //区划编码
				obj.put("BAA400", "01");
				// 判断订单号中是否包含字母，将有字母的订单号排除掉
				boolean res = false;
				for(int j = 0; j < aaz288.length(); j++) {
					if(Character.isLetter(aaz288.charAt(j))) {
						res = true;
						break;
					}
				}
				if(!res) {
					resultArray.add(obj); // 将每一笔订单存入array中
				}
			}
		}
		return resultArray;
	}
	
	/**
	 * 农行对账
	 */
	public void abcStatement(List<Map<String, String>> NHlist, String date, String AAE140, String AAB099){
		if(NHlist != null && NHlist.size() > 0) {
			JSONArray resultArray_ = new JSONArray();
			for(Map<String, String> map : NHlist) {
				String AAE051 = map.get("AAE051"); // 商户号
				String AAB301 = map.get("AAB301"); // 行政区划
				String BAB502 = map.get("BAB502"); //银行类别
				JSONArray resultArray = abcExecute(AAE051, date, AAB301, BAB502); // 执行该商户的对账
				
				if (resultArray.size()>0) {
					for (int i = 0; i < resultArray.size(); i++) {
						resultArray_.add(resultArray.get(i));
					}
				}
			}
			if ("150".equals(AAE140)) {//居民养老
				
				if ("0".equals(AAB099)) {
					API.insertSysRA22(resultArray_);
				} else if ("1".equals(AAB099)) {
					context.getDatas().put("MSG", resultArray_);
					context.getDatas().put("FLAG", "SUCCESS");
				}
			} else if ("110".equals(AAE140)) {//职工养老
				String businessID = "008";
				try {
					ZgssHelper.zgssHttp(businessID, resultArray_.toString());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 农行执行商户下的对账
	 * @param AAE051
	 */
	public JSONArray abcExecute(String AAE051, String date, String AAB301, String BAB502) {
		//使用农行对象
		SettleRequest abcRequest = new SettleRequest();
		Date d = null;
		try {
			d = new SimpleDateFormat("yyyyMMdd").parse(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		//添加时间、是否压缩属性
		abcRequest.dicRequest.put("SettleDate", new SimpleDateFormat("yyyy/MM/dd").format(d)); // 系统日期的前一天yyyy/MM/dd
		abcRequest.dicRequest.put("ZIP", "0");
		//取得交易结果---单商户
		//JSON json = abcRequest.postRequest();
		// 取得交易结果---多商户
		PropertiesUtil proUtil = new PropertiesUtil();
		// 根据商户号获取到所在属性文件中的索引
		int aae051Index = proUtil.getabcAAE051(AAE051);
		System.out.println("农行对账开始========================");
		JSON json = abcRequest.extendPostRequest(aae051Index);
		System.out.println(json);
		System.out.println("获取账单完成=========================");
		//取得交易状态码值
		String code = json.GetKeyValue("ReturnCode");
//		String errMessage = json.GetKeyValue("ErrorMessage");
		JSONArray resultArray = new JSONArray();
		//根据码值判断交易是否成功
		if (code.equals("0000")) {
			//取得交易明细结果集
			String DetailRecords = json.GetKeyValue("DetailRecords");
			DetailRecords = DetailRecords.replace("^^", ",");
			String[] arrays = DetailRecords.split(",");
			for (int i = 1; i < arrays.length; i++) {
				arrays[i] = arrays[i].replace("|", ",");
				String[] array = arrays[i].split(",");
				JSONObject obj = new JSONObject();
				obj.put("AAE051", array[0]); //商户号
				obj.put("AAZ288", array[2]); //订单编号
				obj.put("AAD017", array[3].substring(0, 8)); //交易时间
				obj.put("AAE019", array[4]); //交易金额
				obj.put("AAD008", array[13]); //9014流水号
				obj.put("AAE354", "1"); // 成功标识
				obj.put("AAE355", ""); // 错误信息 
				obj.put("AAZ295", BAB502); // 银行支付类别 
				obj.put("AAE356", "0"); // 退款标识
				obj.put("AAZ2881", ""); // 邮储订单号
				obj.put("AAC003", "");
				obj.put("AAC002", "");
				obj.put("AAB301", AAB301);
				obj.put("BAA400", "01");
				boolean res = false;
				for(int j = 0; j < array[2].length(); j++) {
					if(Character.isLetter(array[2].charAt(j))) {
						res = true;
						break;
					}
				}
				// Sale:支付，Refund：退款，不包含字母的订单存入
				if(array[1] != null && array[1].equals("Sale") && !res) {
					resultArray.add(obj);
					// 支付订单
//					insertRA22(map);
				}
			}
		}
		return resultArray;
	}
	
	/**
	 * 建行对账
	 */
	public void jhStatement(List<Map<String, String>> JHlist, String date, String AAE140, String AAB099){
		if(JHlist != null && JHlist.size() > 0) {
			for(Map<String, String> map : JHlist) {
				String AAE051 = map.get("AAE051"); // 商户号
				String BAE300 = map.get("BAE300"); // 秘钥
				jhExecute(AAE051, BAE300, date); // 执行该商户的对账
			}
		}
	}
	
	/**
	 * 建行对账下载
	 * @param AAE051
	 */
	public void jhExecute(String AAE051, String BAE300, String date) {
		Map<String, String> paramMap = new HashMap<String, String>();
		paramMap.put("version", PayConf.PAY_VERSION);
		paramMap.put("charset", PayConf.CHARSET);
		paramMap.put("signMethod", PayConf.SIGNMETHOD);
		paramMap.put("transCode", "F001"); // 对账文件生成
		paramMap.put("merId", AAE051); // 商户号
		paramMap.put("billDate", date); //对账日期，当前日期的前一天
		paramMap.put("billType", "02"); // 对账单类型
		paramMap.put("fileType", "01"); // 对账单格式
		paramMap.put("transTime", DateUtil.currentTime());
		paramMap.put("backEndUrl", PayConf.DOWNLOAD_BACKEND_URL);
		paramMap.put("reqReserved", "");
		// 签名信息
		String[] paramKeys = new String[] { "version", "charset", "signMethod", "transCode", "merId", "billDate", "billType", "fileType", "transTime", "backEndUrl", "reqReserved"};// 构造签名参数数组
		Map<String, String> paraMap = AppUtils.prepareDataMap(paramMap, paramKeys);//数据预处理
		String mac = AppUtils.signBeforePost(paraMap, BAE300, paramMap.get("charset"));
		paraMap.put("sign", mac);
		
		System.out.println("向清算平台发送交易查询请求:" + AppUtils.restMapToStr(paraMap));// 请求报文
		String res = HttpClientUtil.httpPost(PayConf.DOWNLOAD_URL, paraMap, paramMap.get("charset"));
		System.out.println("清算平台同步响应:" + res);// 返回的响应报文
		
		//签名验证
		boolean validate = AppUtils.validate(res, BAE300, paraMap.get("charset"));
		if(validate){
			Map<String, String> resMap = new HashMap<String, String>();
			AppUtils.convertRetToMap(res, resMap);
			if("00".equals(resMap.get("respCode"))){
				System.out.println("建行对账生成文件成功");
			}else{
				System.out.println("建行对账生成文件失败");
			}
		} else {
			System.out.println("建行对账生成文件验签失败");
		}
	}
	
	/**
	 * 保存对账信息RA22表
	 * @param map
	 */
	private Connection conn = null;
	public void insertRA22(Map<String, String> map) {
		String sql = "INSERT INTO RA22(AAZ288, AAE019, AAD017, AAD008, AAE354, AAE355, AAZ295, AAZ2881, AAE356, AAE051) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement pstat = null;
		try {
			if(conn == null) {
				// 获取数据库连接
				conn = WebHelper.getContext().getConn();
			}
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, map.get("AAZ288")); // 订单号
			pstat.setBigDecimal(2, new BigDecimal(map.get("AAE019"))); // 交易金额
			pstat.setString(3, map.get("AAD017")); // 缴费日期
			pstat.setString(4, map.get("AAD008")); // 平台交易流水号
			pstat.setString(5, map.get("AAE354")); // 成功标识
			pstat.setString(6, map.get("AAE355")); // 错误信息
			pstat.setString(7, map.get("AAZ295")); // 银行支付类别
			pstat.setString(8, map.get("AAZ2881")); // 邮储支付订单ID
			pstat.setString(9, "0"); // 退货标识
			pstat.setString(10, map.get("AAE051")); // 商户号
			pstat.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	public static void main(String[] args) {
		String transName = "IDFR"; // 交易名称
		// 交易数据明文数据封装开始
		String MercCode = "1100529310001000360"; // 商户号 必填  测试数据：1100529310001000003
		String OSttDate = "20161214"; // 当前日期的前一天 必填20140822
		String SetFType = "1"; // 对账文件类型0：全部，1：支付，3：退货
		// 交易数据明文数据封装结束
		// 交易数据明文
		String Plain = "MercCode="+MercCode+"|OSttDate="+OSttDate+"|SetFType="+SetFType;
		
		// 交易数据明文签名
		String Signature = SignatureService.sign(Plain);
		String resultStr= "";//要返回的response信息
		HttpClient httpClient = new HttpClient();
		PostMethod postMethod = new PostMethod(PayConf.PayUrl);
		//填入各个表单域的值
		NameValuePair[] data = {
			new NameValuePair("transName", transName),
			new NameValuePair("Plain", Plain),
			new NameValuePair("Signature", Signature),
		};
		// 将表单的值放入postMethod中
		postMethod.setRequestBody(data);
		try {
			// 执行postMethod
			httpClient.executeMethod(postMethod);
			resultStr= postMethod.getResponseBodyAsString();
			postMethod.releaseConnection();
		} catch (HttpException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		JSONArray resultArray = new JSONArray();
		if(resultStr.indexOf("<ErrorMsg>") != -1) {
			System.out.println(resultStr.substring(resultStr.indexOf("<ErrorMsg>")+10, resultStr.indexOf("</ErrorMsg>")));
		} else {
			// 返回商户对账文件
			String[] array = resultStr.split("\r\n");
			for(int i = 1; i < array.length; i++) {
				JSONObject obj = new JSONObject();
				String items = array[i].replace("|", ",");
				String[] itemsArray = items.split(",");
				obj.put("AAD017", itemsArray[2].toString().substring(0, 8)); // 交易日期
				String aaz288 = itemsArray[3].trim().substring(0, itemsArray[3].trim().length()-7);
				obj.put("AAZ288", aaz288); // 订单号
				obj.put("AAZ2881", itemsArray[3].trim()); // 订单号
				obj.put("AAD008", itemsArray[4].trim()); // 平台流水号
				obj.put("AAE051", itemsArray[5].trim()); // 商户号
				obj.put("AAE019", itemsArray[7].trim()); // 交易金额
				obj.put("AAE354", itemsArray[10].equals("00000000")?"1":"0"); // 回应码
				obj.put("AAE355", ""); // 错误信息
				obj.put("AAC002", ""); // 身份证号
				obj.put("AAZ295", "YC"); // 银行支付类别
				obj.put("AAE356", "0"); // 退款标识
				obj.put("AAC003", "");
				obj.put("AAC002", "");
				// 判断订单号中是否包含字母，将有字母的订单号排除掉
				boolean res = false;
				for(int j = 0; j < aaz288.length(); j++) {
					if(Character.isLetter(aaz288.charAt(j))) {
						res = true;
						break;
					}
				}
				if(!res) {
					resultArray.add(obj); // 将每一笔订单存入array中
				}
				
				System.out.println(resultArray.toJSONString());
			}
		}
	}
}
